﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.Assembly
{
    public partial class panel_F5Plan : UserControl
    {
        private INISettings mySetting;
        private SqlConnection myConnection;
        private SqlConnection masterConnection;
        
        public panel_F5Plan()
        {
            InitializeComponent();

            this.mySetting = new INISettings();
            this.myConnection = new SqlConnection(mySetting.SQLConnectionString);
            this.masterConnection = new SqlConnection(mySetting.DB2ConnectionString);

            LoadData();

            this.F5Plan.DoubleClick += F5Plan_DoubleClick;
            this.F5Plan.PopupMenuShowing += F5Plan_PopupMenuShowing;
            this.f5planContext.ItemClicked += f5planContext_ItemClicked;
        }

        void F5Plan_DoubleClick(object sender, EventArgs e)
        {
            DataRow row = F5Plan.GetFocusedDataRow();
            if (!string.IsNullOrEmpty(row[7].ToString()))
            {
                MessageBox.Show("This plan has been Finished!");
            }
            else
            {
                frm_Edit_F5Plan edit_Form = new frm_Edit_F5Plan();
                edit_Form.planid = Int32.Parse(row[0].ToString());
                if (edit_Form.LoadData())
                {
                    edit_Form.Show();
                }
                else
                {
                    MessageBox.Show("Cannot load data for selected record");
                }
            }             
        }

        void f5planContext_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
        {
            this.f5planContext.Hide();
            DataRow row = F5Plan.GetFocusedDataRow();
            switch (e.ClickedItem.Name)
            {
                case "startSelection":
                    if (row["FinishedDate"] == DBNull.Value)
                    {
                        if (row["CommandDate"] == DBNull.Value)
                        {
                            if (DateTime.Parse(row["DeadLine"].ToString()) > DateTime.Now)
                            {
                                startPlan(row["OrderNo"].ToString());
                            }
                            else
                            {
                                MessageBox.Show("The selected plan is out of Dead Line.");
                            }
                        }
                        else
                        {
                            DateTime start = DateTime.Parse(row["CommandDate"].ToString());
                            MessageBox.Show("The selected plan has been started on " + start.Date.ToShortDateString());
                        }
                    }
                    else
                    {
                        DateTime finish = DateTime.Parse(row["FinishedDate"].ToString());
                        MessageBox.Show("The selected plan has been finished on " + finish.Date.ToShortDateString());
                    }
                    break;
                case "editSelection":                    
                    if (!string.IsNullOrEmpty(row[7].ToString()))
                    {
                        MessageBox.Show("This plan has been Finished!");
                    }
                    else
                    {
                        frm_Edit_F5Plan edit_Form = new frm_Edit_F5Plan();
                        edit_Form.planid = Int32.Parse(row[0].ToString());
                        if (edit_Form.LoadData())
                        {
                            edit_Form.Show();
                        }
                    }
                    break;
                case "refreshSelection":
                    LoadData();
                    break;
            }
        }

        //////////////////
        /// Start F5 Plan
        //////////////////
        private void startPlan(string orderno)
        {
            if (MessageBox.Show("Are you really to start selected plan now?", "Confirm cancel", MessageBoxButtons.YesNo) == DialogResult.Yes)
            {
                try
                {
                    myConnection.Open();
                    string sql = "UPDATE [231_F5Plan] SET CommandDate=@startdate"
                        + " WHERE OrderNo='" + orderno + "' AND CommandDate IS NULL";
                    SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                    cmd_sql.Parameters.Add("@startdate", SqlDbType.DateTime).Value = DateTime.Now.Date;
                    cmd_sql.ExecuteNonQuery();
                    cmd_sql.Dispose();
                    myConnection.Close();
                    MessageBox.Show("The selected plan is now started successfully.");
                }
                catch
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    MessageBox.Show("Cannot start selected plan. Please try again.");
                }
            }
        } 

        void F5Plan_PopupMenuShowing(object sender, DevExpress.XtraGrid.Views.Grid.PopupMenuShowingEventArgs e)
        {
            this.f5planContext.Show(MousePosition);
        }

        ///////////////////////////////////
        /// Load Data From Local Database
        ///////////////////////////////////
        private void LoadData()
        {
            DataSet sourceDataSet = new DataSet();
            SQLManagement mySQLManagement = new SQLManagement();
            mySQLManagement.SQLConnectionString = mySetting.SQLConnectionString;
            string sql = "SELECT [231_F5Plan].ID, [231_F5Plan].OrderNo, [231_F5Plan].PlanNo, [231_F5Plan].PartNo,"
                + " [231_F5Plan].CommandQuantity, [231_F5Plan].CommandDate, [231_F5Plan].DeadLine, [231_F5Plan].FinishedDate,"
                + " [231_F5Plan].FinishedQuantity, [231_F5Plan].FromSerialNo, [231_F5Plan].ToSerialNo,	[222_Customer].FullName,"
                + " [231_F5Plan].Remark"
                + " FROM [231_F5Plan] LEFT OUTER JOIN [222_Customer] ON [222_Customer].CustomerID = [231_F5Plan].CustomerID"
                + " ORDER BY [231_F5Plan].OrderNo";
            sourceDataSet = mySQLManagement.SQL_Query(sql);
            if (sourceDataSet != null)
            {
                gridF5Plan.DataSource = sourceDataSet.Tables[0];
                getUpdateInfo();
            }
            else
            {
                DataTable newTable = new DataTable();
                newTable.Columns.Add("ID", typeof(string));
                newTable.Columns.Add("OrderNo", typeof(string));
                newTable.Columns.Add("PlanNo", typeof(string));
                newTable.Columns.Add("PartNo", typeof(string));
                newTable.Columns.Add("CommandQuantity", typeof(int));
                newTable.Columns.Add("CommandDate", typeof(DateTime));
                newTable.Columns.Add("DeadLine", typeof(DateTime));
                newTable.Columns.Add("FinishedDate", typeof(DateTime));
                newTable.Columns.Add("FinishedQuantity", typeof(int));
                newTable.Columns.Add("FromSerialNo", typeof(string));
                newTable.Columns.Add("ToSerialNo", typeof(string));
                newTable.Columns.Add("Customer", typeof(string));
                newTable.Columns.Add("Remark", typeof(string));
                gridF5Plan.DataSource = newTable;
            }
            F5Plan.Columns[0].Caption = "ID";
            F5Plan.Columns[1].Caption = "Order No.";
            F5Plan.Columns[2].Caption = "Plan No.";
            F5Plan.Columns[3].Caption = "Part No.";
            F5Plan.Columns[4].Caption = "Command Quantity";
            F5Plan.Columns[5].Caption = "Command Date";
            F5Plan.Columns[6].Caption = "Dead Line";
            F5Plan.Columns[7].Caption = "Finished Date";
            F5Plan.Columns[8].Caption = "Finished Quantity";
            F5Plan.Columns[9].Caption = "From Serial No.";
            F5Plan.Columns[10].Caption = "To Serial No.";
            F5Plan.Columns[11].Caption = "Customer";
            F5Plan.Columns[12].Caption = "Remark";
            F5Plan.Columns[0].Visible = false;
            F5Plan.BestFitColumns();
        }

        /////////////////////////////////////
        /// Get Update Information
        /////////////////////////////////////
        private void getUpdateInfo()
        {
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT LastUpdate FROM [222_UpdateHistory] WHERE ID=8";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                if (dataReader.Read())
                {
                    this.updateDate.Text = dataReader["LastUpdate"].ToString();
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot get update information");
            }
        }

        private void buttonRefresh_Click(object sender, EventArgs e)
        {
            LoadData();
        }


        private void buttonEdit_Click(object sender, EventArgs e)
        {            
            DataRow row = F5Plan.GetFocusedDataRow();
            if (!string.IsNullOrEmpty(row[7].ToString()))
            {
                MessageBox.Show("This plan has been Finished!");
            }
            else
            {
                frm_Edit_F5Plan edit_Form = new frm_Edit_F5Plan();
                edit_Form.planid = Int32.Parse(row[0].ToString());
                if (edit_Form.LoadData())
                {
                    edit_Form.Show();
                }
                else
                {
                    MessageBox.Show("Cannot load data for selected record");
                }
            } 
        }

        ////////////////////////////////////
        /// Update database From AS400
        ////////////////////////////////////

        private void buttonImport_Click(object sender, EventArgs e)
        {
            DataSet as400 = new DataSet();
            DataSet f5plan = new DataSet();
            string sql = null;
            SqlCommand cmd;
            SqlDataAdapter adapter;
            DataTable f5plan_db2;
            DataTable f5plan_local;                
            try
            {
                //Get Data From AS400                
                masterConnection.Open();
                sql = "SELECT * FROM OPENQUERY(AS400_MPFLIB, 'SELECT MPOR00.*"
                    + " FROM MPOR00 LEFT JOIN"
                    + " MPIT00 ON MPIT00.ITHNBN=MPOR00.ORHNBN"
                    + " WHERE SUBSTRING(MPIT00.ITFILL,16,4)=''0231'' AND MPOR00.ORHNTP=''M''')";
                cmd = new SqlCommand(sql, masterConnection);
                adapter = new SqlDataAdapter();
                adapter.SelectCommand = cmd;
                adapter.Fill(as400);
                f5plan_db2 = as400.Tables[0];
                adapter.Dispose();
                cmd.Dispose();
                masterConnection.Close(); 
                
                //Get Data From Local Database
                myConnection.Open();
                sql = "SELECT * FROM [231_F5Plan]";
                cmd = new SqlCommand(sql, myConnection);
                adapter = new SqlDataAdapter();
                adapter.SelectCommand = cmd;
                adapter.Fill(f5plan, "231_F5Plan");
                f5plan_local = f5plan.Tables["231_F5Plan"];
                                
                foreach (DataRow as400_row in f5plan_db2.Rows)
                {
                    DataRow[] current_row = f5plan_local.Select("OrderNo='" + as400_row["ORODNO"].ToString() + "'");
                    if (current_row.Length > 0)
                    {
                        foreach (DataRow local_row in current_row)
                        {
                            local_row.BeginEdit();
                            local_row["PartNo"] = as400_row["ORHNBN"].ToString();
                            local_row["Quantity"] = as400_row["ORHTSU"].ToString();                            
                            local_row.EndEdit();
                        }
                    }
                    else
                    {
                        DataRow new_row = f5plan_local.NewRow();
                        new_row["OrderNo"] = as400_row["ORODNO"].ToString();
                        new_row["PartNo"] = as400_row["ORHNBN"].ToString();
                        new_row["Quantity"] = as400_row["ORHTSU"].ToString();
                        f5plan_local.Rows.Add(new_row);
                    }
                }  
                               

                SqlCommandBuilder objCommandBuilder = new SqlCommandBuilder(adapter);
                //Update to Local Database
                adapter.Update(f5plan, "231_F5Plan");
                
                adapter.Dispose();
                cmd.Dispose();
                myConnection.Close();
                
                //Update History
                myConnection.Open();
                sql = "UPDATE [222_UpdateHistory] SET LastUpdate=GETDATE() WHERE ID=8";
                SqlCommand cmd_sql = new SqlCommand(sql, myConnection);                
                cmd_sql.ExecuteNonQuery();
                cmd_sql.Dispose();
                myConnection.Close();

                LoadData();

                MessageBox.Show("Database has been updated successfull.");
                                
            }
            catch (Exception ex)
            {
                if (masterConnection.State == ConnectionState.Open)
                {
                    masterConnection.Close();
                }

                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show(ex.Message);
                //MessageBox.Show("Cannot connect to AS400");
            }
        }
    }
}
